/*
 *提供连接到数据库的方法
 *
 */
package com.res0w.SMMS.factory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author res0w
 * @since 2009-9-14
 * @version 0.2
 */
public class DataBaseOperate {
    /*
     *使用默认连接
     */

    public DataBaseOperate() {
        //连接数据库
        DataBaseConnector.DBConnect();
    }

    /*
     * 使用自定义连接方式
     */
    public DataBaseOperate(String drivername, String url,
            String username, String password) {
        DataBaseConnector.DBConnect(drivername, url, username, password);
    }

    /*
     * 创建并使用数据库
     * 注意：此方法在正式版本中应删除，或者独立程序出现；
     * 对数据库连接中“URL”的设置应该去除类似“SMMS”的内容
     * @param sql0 创建数据库语句
     * @param sql1 使用数据库语句
     */
    public boolean createDataBase(String dbname) {
        boolean re = true;
        String sql0 = null;
        String sql1 = null;
        sql0 = "create schema " + dbname + "";
        sql1 = "use " + dbname + "";
        try {
            System.out.println(sql0);
            System.out.println(sql1);
            DataBaseConnector.DBSqlUpdate(sql0);
            DataBaseConnector.DBSqlUpdate(sql1);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("添加数据库出错！");
            re = false;
        }
        DataBaseConnector.DBClosed();
        return re;
    }

    public boolean createTable() {
        boolean re = true;
        String sql = null;
        re = DataBaseConnector.DBProcedureExe("CALL smms.createTable()");
        DataBaseConnector.DBClosed();
        return re;

    }
    /*查询默认返回第一列
     */

    public ArrayList dbQuerySolo(String sql, boolean canClose) throws SQLException {
        ArrayList list = new ArrayList();
        ResultSet rs = null;
//        DataBaseConnector.DBConnect();
        try {
            rs = DataBaseConnector.DBSqlQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
        }
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        if (canClose) {
            DataBaseConnector.DBClosed();
        }
        return list;
    }

    public ResultSet dbQueryMuti(String sql) throws SQLException {
        ResultSet rs = null;
//        DataBaseConnector.DBConnect();
        try {
            rs = DataBaseConnector.DBSqlQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public void dbUpdateBatch(ArrayList sqls) {
        DataBaseConnector.DBConnect();
        for (int i = 0; i < sqls.size(); i++) {
            try {
                DataBaseConnector.DBBatchAdd(sqls.get(i).toString().trim());
            } catch (SQLException ex) {
                Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        try {
            DataBaseConnector.DBBatchExe();
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
        }
        DataBaseConnector.DBClosed();
    }
    public void dbExe(String sql){
        try {
            DataBaseConnector.DBSqlExe(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void dbClosed(){
        DataBaseConnector.DBClosed();
    }
}

class DataBaseConnector {

//    private static String SQL;
    private static Connection conn;
    private static Statement stmt;
    private static ResultSet rs = null;
    private static String driverName = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/smms";
    private static String user = "root";
    private static String password = "881010";
    /*
     * 使用默认配置
     * 测试时使用
     */

    public static void DBConnect() {
        //TODO预先读取配置文件内容
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库驱动加载失败！");
        }
    }

    public static void DBConnect(String drivername, String url,
            String username, String password) {
        try {
            Class.forName(drivername);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库驱动加载失败！");
        }
    }

    public static void DBClosed() {
        try {
            //rs.close()；
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseOperate.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /*
     * 返回一个RS类型数据
     *
     */
    public static ResultSet DBSqlQuery(String sql) throws SQLException {
//        SQL = sql;
        rs = stmt.executeQuery(sql);
        return rs;
    }

    /*
     * 返回多个结果，用于存储过程，详见API文档
     */
    public static void DBSqlExe(String sql) throws SQLException {
//        SQL = sql;
        boolean r;
        r = stmt.execute(sql);
    }

    /*
     *没有返回值的SQL语句，比如add,del等操作
     *
     */
    public static void DBSqlUpdate(String sql) throws SQLException {
//        SQL=sql;
        stmt.executeUpdate(sql);
    }

    public static ResultSet DBReturnRS() {
        return rs;
    }

    /*
     * 没有参数的存储过程方法
     */
    public static boolean DBProcedureExe(String procedureSql) {
        boolean re = true;
        try {
            java.sql.CallableStatement callstmt = conn.prepareCall(procedureSql);
            callstmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseConnector.class.getName()).log(Level.SEVERE, null, ex);
            re = false;
        }
        return re;
    }

    public static void DBBatchAdd(String sql) throws SQLException {
        stmt.addBatch(sql);
    }

    public static void DBBatchExe() throws SQLException {
        stmt.executeBatch();
    }
}
